package com.nbjtjc.safe.mapper.cost;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.nbjtjc.safe.model.cost.CostBudgetItem;
import com.nbjtjc.safe.model.cost.CostBudgetPlan;
import com.nbjtjc.util.MyMapper;
@Mapper
public interface CostBudgetItemMapper extends MyMapper<CostBudgetItem> {
//	@Select("select * from cost_budget_item where cancel = 0 and budget_plan_id = #{planId}")
	List<CostBudgetItem> findByPlanId(@Param("planId") int planId);
	
	List<CostBudgetItem> findDetailByPlanId(@Param("planId") int planId);
	
	@Select("select id from cost_budget_item where cancel = 0 and budget_plan_id = #{planId}")
	ArrayList<Integer> findIdByPlanId(@Param("planId") int planId);
	
	CostBudgetItem show(@Param("id") int id);
	
	@Update("UPDATE cost_budget_item SET `item_current_budget`=(SELECT SUM(IFNULL(item_budget,0)) FROM cost_budget_sub_item WHERE cancel = 0 and budget_item_id = #{id}) WHERE id=#{id}")
	int updateCurrentBudget(@Param("id") int id);
	
	@Update("UPDATE cost_budget_item SET account_sum = (SELECT sum(ifnull(account_sum,0)) FROM cost_budget_sub_item WHERE cancel = 0 and budget_item_id = (SELECT budget_item_id FROM cost_budget_sub_item WHERE id=#{subId})),account_remain = `item_total_budget`-`account_sum`   where cancel = 0 and id = (SELECT budget_item_id FROM cost_budget_sub_item WHERE id=#{subId})")
	int updateAccountSum(@Param("subId") int subId);
	
	@Insert("insert into `cost_budget_item` (budget_plan_id,item,item_temp_id)  ( SELECT #{cbp.id} AS `budget_plan_id`,`item`,`id` AS `item_temp_id` FROM `cost_budget_plan_templet` WHERE cancel = 0 and parent_id = -1)")
	int insertItemByTemplet(@Param("cbp") CostBudgetPlan costBudgetPlan);

	
	@Insert("insert into cost_budget_month(plan_id,item_id,item_type,`year`)"
            +"select #{cbp.id} as plan_id,id as item_id,1 as item_type,#{cbp.year} as `year` from cost_budget_item where cancel = 0 and budget_plan_id = #{cbp.id}")
	int insertItemMonth(@Param("cbp") CostBudgetPlan costBudgetPlan);
	
	@Insert("insert into cost_budget_month(plan_id,item_id,item_type,`year`,`item_temp_id`)"
            +"select #{cbp.id} as plan_id,id as item_id,1 as item_type,#{cbp.year} as `year`,`item_temp_id` from cost_budget_item where cancel = 0 and budget_plan_id = #{cbp.id}")
	int insertItemMonthTemplet(@Param("cbp") CostBudgetPlan costBudgetPlan);


	@Update(" update cost_budget_item b inner join "
+" (SELECT SUM(IFNULL(a.account_sum,0)) AS account_sum,c.id AS id FROM cost_budget_sub_item a "
+"  INNER JOIN cost_budget_item c ON a.budget_item_id = c.id  WHERE c.budget_plan_id = #{planId} AND a.`cancel`=0 AND c.`cancel`=0 GROUP BY c.id ) u "
+" on  b.id=u.id "
+" set b.`account_sum`= u.account_sum ,b.`account_remain`= b.`item_total_budget`- u.account_sum")
	int updateAccountSumByPlanId(@Param("planId") int planId);
	
	@Select(" SELECT * FROM cost_budget_item a INNER JOIN cost_budget_sub_item b ON a.id = b.budget_item_id WHERE b.id=#{subId} LIMIT 1")
	CostBudgetItem findBySubItemId(@Param("subId")int subId);
}